﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using KillParser;

using System.IO;

namespace Killboard.Models
{
    public class KillRepository
    {
        private KillsDataContext dc;

        public KillRepository()
        {
            dc = new KillsDataContext();

            //FileInfo f = new FileInfo(string.Format(@"C:\sql {0:hh-mm-ss}.out", DateTime.Now));
            //dc.Log = f.CreateText();

            StringWriter sw = new StringWriter();
            dc.Log = sw;
        }

        ~KillRepository()
        {
            System.Diagnostics.Debug.WriteLine(dc.Log.ToString());
        }

        public KillRepository(string connectionString)
        {
            dc = new KillsDataContext(connectionString);
        }

        public int? AddKill(KillMail mail)
        {
            int? victimPilotID = FindPilot(mail.Victim.Name);

            if (victimPilotID == null)
            {
                victimPilotID = AddPilot(mail.Victim);
            }

            int? victimCorpID = FindCorp(mail.Victim.CorpName);


            return null;
        }

        private int? AddPilot(Pilot pilot)
        {
            ekbPilot newPilot = new ekbPilot();

            newPilot.PilotName = pilot.Name;

            dc.ekbPilots.InsertOnSubmit(newPilot);
            dc.SubmitChanges();

            
            return newPilot.__ID;
        }

        private int? FindCorp(string corpName)
        {
            var corp = from c in dc.ekbPlayerCorps
                       where c.CorpName == corpName
                       select c;

            if (corp.Count() == 0)
            {
                return null;
            }

            return corp.First().__ID;
        }

        public int FindOrAddPilot(string name, int? eveID)
        {
            int? pilotNumber = FindPilot(name);
            if (pilotNumber == null)
            {
                pilotNumber = AddPilot(name, eveID);
            }
            else
            {
                CheckAndUpdatePilot(pilotNumber, eveID);
            }
            
           


            return (int)pilotNumber;
        }


        private void CheckAndUpdatePilot(int? pilotNumber, int? eveID)
        {
            var pilots = from p in dc.ekbPilots
                        where p.__ID == pilotNumber
                        select p;

            var pilot = pilots.First();
            pilot.eveID = eveID;

            dc.SubmitChanges();
            
        }



        public int? FindPilot(string name)
        {
            var pilot = from p in dc.ekbPilots
                        where p.PilotName == name
                        select p;

            if (pilot.Count() == 0)
            {
                return null;
            }

            return pilot.First().__ID;
        }

        internal ekbPlayerCorp FindPlayerCorp(string p)
        {
            var corp = from c in dc.ekbPlayerCorps
                       where c.CorpName == p
                       select c;

            if (corp.Count() == 0)
            {
                return null;
            }
            else
            {
                return corp.First();
            }
        }

        internal ekbPlayerCorp FindNPCCorp(string p)
        {
            var corp = from c in dc.eveNames
                       where c.itemName == p
                       select c;

            if (corp.Count() == 0)
            {
                return null;
            }
            else
            {
                ekbPlayerCorp fakePlayerCorp = new ekbPlayerCorp();
                fakePlayerCorp.__ID = corp.First().itemID;
                return fakePlayerCorp;
            }
        }

        public ekbPlayerCorp AddPlayerCorp(string corpName, string AllianceName)
        {
            ekbPlayerCorp pc = new ekbPlayerCorp();

            if (AllianceName != null && !AllianceName.Equals("none", StringComparison.CurrentCultureIgnoreCase))
            {

                ekbAlliance playerAlliance = FindAlliance(AllianceName);

                if (playerAlliance == null)
                {
                    playerAlliance = AddAlliance(AllianceName);
                }

                pc.AllianceID = playerAlliance.__ID;
            }
            else
            {
                pc.AllianceID = null;
            }

            pc.CorpName = corpName;
            
            dc.ekbPlayerCorps.InsertOnSubmit(pc);
            dc.SubmitChanges();

            return pc;
            
        }

        

        public ekbAlliance FindAlliance(string AllianceName)
        {
            var Ally = from c in dc.ekbAlliances
                       where c.AllianceName == AllianceName
                       select c;

            if (Ally.Count() == 0)
            {
                return null;
            }
            else
            {
                return Ally.First();
            }
        }

        private ekbAlliance AddAlliance(string AllianceName)
        {
            ekbAlliance ally = new ekbAlliance();
            ally.AllianceName = AllianceName;

            dc.ekbAlliances.InsertOnSubmit(ally);
            dc.SubmitChanges();

            return ally;
        }

        internal invType FindItem(string p)
        {
            if (p == null)
            {
                return null;
            }

            var eveItem = from i in dc.invTypes
                          where i.typeName == p.Trim()
                          select i;

            if (eveItem.Count() == 0)
            {
                return null;
            }
            else
            {
                return eveItem.First();
            }
        
        }

        internal int SaveKill(KillMail killMail)
        {
            ekbKill kill = new ekbKill();

            var killExists = from k in dc.ekbKills
                             where k.KillTimeUTC.Equals(killMail.KillTimeUTC) && k.VictimID.Equals(killMail.Victim.PilotID)
                             select k;

            if (killExists.Count() > 0)
            {
                return killExists.First().__ID;
            }

            kill.KillTimeUTC = killMail.KillTimeUTC;
            kill.VictimID = (int)killMail.Victim.PilotID;
            kill.VictimAllianceID = (killMail.Victim.AllianceID == null ? -1 : (int)killMail.Victim.AllianceID);
            kill.VictimCorpID = (killMail.Victim.CorpID == null ? -1 : (int)killMail.Victim.CorpID);
            kill.SolarSystemID = (killMail.FightSystem.SolSystemID == null ? -1 : (int) killMail.FightSystem.SolSystemID);
            kill.VictimShipID = killMail.Victim.ShipID;
            kill.DamageTaken = killMail.Victim.DamageTakenOrDone;

            foreach (Pilot killer in killMail.Involved)
            {
                ekbInvolved involved = new ekbInvolved();

                if (!killer.IsNPC)
                {
                    involved.AllianceID = killer.AllianceID;
                    involved.secStatus = (decimal)killer.SecStatus;
                    involved.PilotID = (int)killer.PilotID;
                    involved.npcID = null;
                }
                else
                {
                    involved.npcID = (short)killer.PilotID;
                }

                involved.LaidFinalBlow = killer.LaidTheFinalBlow;
                involved.CorpID = killer.CorpID;
                involved.WeaponID = killer.WeaponID;
                
                involved.ShipID = killer.ShipID;
                involved.isNPC = killer.IsNPC;
                involved.DamageDone = killer.DamageTakenOrDone;

                kill.ekbInvolveds.Add(involved);
            }

            foreach (Item loot in killMail.ItemCasualties)
            {
                ekbItemDrop drop = new ekbItemDrop();

                drop.DestroyedFlag = loot.WasDestroyed;
                drop.CargoFlag = loot.InCargo;
                drop.ItemID = loot.DbItem.typeID;
                drop.itemQty = loot.ItemQty;

                kill.ekbItemDrops.Add(drop);
            }


            dc.ekbKills.InsertOnSubmit(kill);
            
            dc.SubmitChanges();

            return kill.__ID;
            


        }


        internal int? AddPilot(string pilotName, int? eveID)
        {
            ekbPilot pilot = new ekbPilot();
            pilot.PilotName = pilotName;
            pilot.eveID = eveID;

            dc.ekbPilots.InsertOnSubmit(pilot);
            dc.SubmitChanges();

            return pilot.__ID;
        }

        internal int? FindSolarSystem(string systemName)
        {
            var solSystem = from s in dc.mapSolarSystems
                            where s.solarSystemName == systemName
                            select s;

            if (solSystem.Count() == 0)
            {
                return null;
            }
            else
            {
                return solSystem.First().solarSystemID;
            }
        }

        internal ekbKill GetKill(int id)
        {
            var kills = from k in dc.ekbKills
                        where k.__ID == id
                        select k;

            return kills.First<ekbKill>();
        }

        public string GetCorpName(int id)
        {
            var corp = from c in dc.ekbCorpViews
                       where c.corpid == id
                       select c;



            return corp.First().corpname;
        }

        public string GetAllianceName(int id)
        {
            var alliance = from a in dc.ekbAlliances
                           where a.__ID == id
                           select a;

            return alliance.First().AllianceName;
        }

        public string GetItemName(short? id)
        {
            invType type = GetInvType(id);

            if (type == null)
            {
                return "Ship ID not found";
            }

            return type.typeName;

        }

        public invType GetInvType(short? id)
        {
            var types = from t in dc.invTypes
                        where t.typeID == id
                        select t;

            if (types.Count() != 0)
            {
                return types.First();
            }
            else
            {
                return null;
            }

        }

        public int GetIntAttribute(short? typeID, string attributeName)
        {
            var attrib = from a in dc.dgmAttributeTypes
                         where a.attributeName == attributeName
                         select a;

            int attrNum = attrib.First().attributeID;

            var value = from v in dc.dgmTypeAttributes
                        where v.typeID == typeID && v.attributeID == attrNum
                        select v;

            return (int)value.First().valueInt;
        }

        public int GetMetaLevel(short? typeID)
        {
            string attributeName = "metaLevel";
            var attrib = from a in dc.dgmAttributeTypes
                         where a.attributeName == attributeName
                         select a;

            int attrNum = attrib.First().attributeID;

            var value = from v in dc.dgmTypeAttributes
                        where v.typeID == typeID && v.attributeID == attrNum
                        select v;

            dgmTypeAttribute dta = value.First();

            if(dta.valueInt != null)
            {
                return (int)dta.valueInt;
            }
            else if(dta.valueFloat != null)
            {
                return (int)dta.valueFloat;
            }

            return 0;

        }


        internal ekbKill GetKillByDate(DateTime killUTC)
        {
            var kill = from k in dc.ekbKills
                       where k.KillTimeUTC.Equals(killUTC)
                       select k;

            if (kill.Count() > 0)
            {
                return kill.First();
            }
            else
            {
                return null;
            }
        }

        internal int FindOrAddCorp(string corpName)
        {
            int? corpID = FindCorp(corpName);
            if (corpID == null)
            {
                corpID = AddPlayerCorp(corpName);
            }

            return (int)corpID;
            
        }

        private int? AddPlayerCorp(string corpName)
        {
            ekbPlayerCorp pc = new ekbPlayerCorp();
            pc.AllianceID = null;
            pc.CorpName = corpName;

            dc.ekbPlayerCorps.InsertOnSubmit(pc);
            dc.SubmitChanges();

            return (int)pc.__ID;
        }

        internal int FindOrAddAlliance(string allianceName)
        {
            
            ekbAlliance alliance = FindAlliance(allianceName);
            if (alliance == null)
            {
                alliance = AddAlliance(allianceName);
            }


            return alliance.__ID;
        }

        internal void AddKill(ekbKill eKill)
        {
            dc.ekbKills.InsertOnSubmit(eKill);
            dc.SubmitChanges();
        }
        public IEnumerable<Models.invGroup> GetShipTypes()
        {
            var ships = from c in dc.invGroups
                        where c.categoryID == 6
                        select c;

            return ships;
        }

        public IEnumerable<Models.KillStats> SumKillsByShip(int week, int year)
        {
            ekbConfig config = GetConfiguration();
            
            IEnumerable<Models.invGroup> shipTypes = GetShipTypes();
            var weeklyKills = GetKillsForWeek(week, year);

            List<KillStats> killsByShip = new List<KillStats>();

            foreach (var shipType in shipTypes)
            {
                KillStats kill = new KillStats();
                kill.CatName = shipType.groupName;

                var sumLosses = from c in weeklyKills
                          where c.invType.invGroup.groupID == shipType.groupID &&
                                c.VictimCorpID == config.OwningCorpID
                          select c;

                int losses = sumLosses.Count();

                var sumKills = from c in weeklyKills
                          where c.invType.invGroup.groupID == shipType.groupID &&
                                c.VictimCorpID != config.OwningCorpID
                          select c;

                int kills = sumKills.Count();

                kill.Kills = kills;
                kill.Losses = losses;

                killsByShip.Add(kill);
            }

            return killsByShip;

        }

        public IEnumerable<Models.KillStats> SumKillsByShip()
        {
            ekbConfig config = GetConfiguration();

            IEnumerable<Models.invGroup> shipTypes = GetShipTypes();
            List<KillStats> killsByShip = new List<KillStats>();

            foreach (var shipType in shipTypes)
            {
                KillStats kill = new KillStats();
                kill.CatName = shipType.groupName;

                var sumLosses = from c in dc.ekbKills
                                where c.invType.invGroup.groupID == shipType.groupID &&
                                      c.VictimCorpID == config.OwningCorpID
                                select c;

                int losses = sumLosses.Count();

                var sumKills = from c in dc.ekbKills
                               where c.invType.invGroup.groupID == shipType.groupID &&
                                     c.VictimCorpID != config.OwningCorpID
                               select c;

                int kills = sumKills.Count();

                kill.Kills = kills;
                kill.Losses = losses;

                killsByShip.Add(kill);
            }

            return killsByShip;

        }

        
        
        public IQueryable<ekbKill> GetKillsForWeek(int week, int year)
        {
            System.Globalization.CultureInfo myCI = new System.Globalization.CultureInfo("en-US");

            week--;

            DateTime start = ekbCalendar.GetBeginningDate(week, year);
            DateTime end = start.Add(new TimeSpan(6, 0, 0, 0));


            var killz = from c in dc.ekbKills
                        where c.KillTimeUTC >= start && c.KillTimeUTC <= end
                        select c;


            return killz;
        }
        public object GetMostRecentKills(int count)
        {

            int myCorp = GetMyCorpFromConfig();

            var killz = from c in dc.ekbKills
                        where c.VictimCorpID != myCorp
                        orderby c.KillTimeUTC descending
                        select c;

            return killz.Take(count);
        }
        
        
        private int GetMyCorpFromConfig()
        {
            var config = from c in dc.ekbConfigs
                         where c.configActive == true
                         select c;

            if (config.Count() > 1)
            {
                throw new TooManyConfigsException();
            }

            if (config.Count() == 0)
            {
                throw new NoConfigException();
            }

            if (config.First().OwningCorpID == null)
            {
                return 0;
            }
            else
            {
                return (int)config.First().OwningCorpID;
            }
        }

        public int? GetNPCID(string name)
        {

            var npcID = from c in dc.invTypes
                        where c.typeName == name
                        select c;

            if (npcID.Count() == 0)
            {
                return null;
            }
            else
            {
                return npcID.First().typeID;
            }
        }

        public string GetNPCCorp(int corpID)
        {
            var corp = from c in dc.ekbCorpViews
                       where c.corpid == corpID
                       select c;

            if (corp.Count() == 0)
            {
                return "unknown";
            }
            else
            {
                return corp.First().corpname;
            }
        }
        public bool CheckPassword(string postingPass)
        {

            ekbConfig config = GetConfiguration();

            string password = config.PostingPassword;

            if (postingPass.Trim().Equals(password))
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        public bool CheckAdminPassword(string adminPassword)
        {
            ekbConfig config = GetConfiguration();
            if(config.AdminPassword.Trim().Equals(adminPassword))
            {
                return true;
            }

            return false;
        }
        public ekbConfig GetConfiguration()
        {
            var config = from c in dc.ekbConfigs
                         where c.configActive == true
                         select c;

            if (config.Count() == 0)
            {
                throw new NoConfigException();
            }
            else
            {
                return config.First();
            }
        }
        public void DeactivateConfigs()
        {
            var x = from c in dc.ekbConfigs
                    where c.configActive == true
                    select c;

            foreach(ekbConfig config in x)
            {
                config.configActive = false;
            }

            dc.SubmitChanges();
            
        }
        public void SaveNewConfig(ekbConfig config)
        {   
            dc.ekbConfigs.InsertOnSubmit(config);
            dc.SubmitChanges();
        }

        public IQueryable<ekbKill> GetKillsAndLossesByType(string shipType)
        {
            ekbConfig config = GetConfiguration();

            var kills = from c in dc.ekbKills
                        where c.invType.invGroup.groupName == shipType.Trim()
                        select c;

            return kills;
                        



        }
        
        public IQueryable<ekbKill> GetAllKills()
        {
            ekbConfig config = GetConfiguration();
            var kills = from c in dc.ekbKills
                        where c.VictimCorpID != config.OwningCorpID
                        select c;

            return kills;
        }


        public IQueryable<ekbKill> GetAllLosses()
        {
            ekbConfig config = GetConfiguration();
            var kills = from c in dc.ekbKills
                        where c.VictimCorpID == config.OwningCorpID
                        select c;

            return kills;
        }
        
        public IEnumerable<Models.KillStats> SumPilotKillsByShip(IQueryable<ekbKill> killQuery, int pilotID)
        {
            IEnumerable<Models.invGroup> shipTypes = GetShipTypes();
            List<KillStats> killsByShip = new List<KillStats>();

            foreach (var shipType in shipTypes)
            {
                KillStats kill = new KillStats();
                kill.CatName = shipType.groupName;

                var sumLosses = from c in killQuery
                                where c.invType.invGroup.groupID == shipType.groupID &&
                                c.VictimID == pilotID
                                select c;

                int losses = sumLosses.Count();

                var sumKills = from c in dc.ekbKills
                               join involved in dc.ekbInvolveds on c.__ID equals involved.KillID
                               where c.invType.invGroup.groupID == shipType.groupID && involved.PilotID == pilotID
                               select c;

                int kills = sumKills.Count();

                kill.Kills = kills;
                kill.Losses = losses;

                killsByShip.Add(kill);
            }

            return killsByShip;
                        
        }


        public IQueryable<ekbKill> GetKillsAndLossesForPilot(int pilotID)
        {

            var kills = from kill in dc.ekbKills
                        join involved in dc.ekbInvolveds on kill.__ID equals involved.KillID
                        where involved.PilotID == pilotID
                        select kill;

            var losses = from kill in dc.ekbKills
                         where kill.VictimID == pilotID
                         select kill;


            var all = kills.Union(losses);

            return all;
   
        }

        public ekbPilot GetPilot(string pilotName)
        {
            var pilot = from pilots in dc.ekbPilots
                         where pilots.PilotName.Equals(pilotName)
                         select pilots;

            return pilot.First();
        }


        public IEnumerable<WeekyTop> GetTopKillersForWeek()
        {
            DateTime weekBeginning = ekbCalendar.GetBeginningofCurrentWeek();

            ekbConfig config = GetConfiguration();

            
            
 
            /*  The following code works fine in SQL 2008, however a bug in SQL 2005 does not allow
             * this to work.
             * 
             * 
            var topKillers = from pilot in dc.ekbInvolveds
                             join pilotInfo in dc.ekbPilots on pilot.PilotID equals pilotInfo.__ID
                             join kill in dc.ekbKills on pilot.KillID equals kill.__ID
                             where kill.KillTimeUTC >= weekBeginning &&
                             pilot.CorpID == config.OwningCorpID
                             group pilot by pilot.ekbPilot.PilotName
                             into grp
                             select new WeekyTop{PilotName = grp.Key, KillLossTotal = grp.Distinct().Count()};
            
            return topKillers.OrderByDescending(c => c.KillLossTotal).Take(10);
            */

            dc.Log.Flush();


            var killsForDate = from pilot in dc.ekbInvolveds
                               join pilotInfo in dc.ekbPilots on pilot.PilotID equals pilotInfo.__ID
                               join kill in dc.ekbKills on pilot.KillID equals kill.__ID
                               where kill.KillTimeUTC >= weekBeginning &&
                               pilot.CorpID == config.OwningCorpID
                               select pilot;

            killsForDate.Count();

            
            var topKillers = from pilots in killsForDate
                             group pilots by pilots.ekbPilot.PilotName
                                 into grp
                                 select new WeekyTop { PilotName = grp.Key, KillLossTotal = grp.Distinct().Count() };

            string sqlLog = dc.Log.ToString();
            
            return topKillers.OrderByDescending(c => c.KillLossTotal).Take(10);

                              

        }
        public IEnumerable<WeekyTop> GetTopLosersForWeek()
        {
            DateTime weekBeginning = ekbCalendar.GetBeginningofCurrentWeek();

            ekbConfig config = GetConfiguration();

            //var topLosers = from kill in dc.ekbKills
            //                join pilot in dc.ekbPilots on kill.VictimID equals pilot.__ID
            //                where kill.VictimCorpID == config.OwningCorpID
            //                && kill.KillTimeUTC >= weekBeginning
            //                group pilot by pilot.PilotName
            //                    into grp
            //                    select new WeekyTop { PilotName = grp.Key, KillLossTotal = grp.Distinct().Count() };

            //return topLosers.OrderByDescending(c => c.KillLossTotal).Take(10);


            var lossesForDate =
                from kill in dc.ekbKills
                join pilotInfo in dc.ekbPilots on kill.VictimID equals pilotInfo.__ID
                where kill.KillTimeUTC >= weekBeginning &&
                kill.VictimCorpID == config.OwningCorpID
                select kill;

            lossesForDate.Count();


            var topLosers = from losses in lossesForDate
                            group losses by losses.ekbPilot.PilotName
                                 into grp
                                 select new WeekyTop { PilotName = grp.Key, KillLossTotal = grp.Distinct().Count() };

            string sqlLog = dc.Log.ToString();

            return topLosers.OrderByDescending(c => c.KillLossTotal).Take(10);

            


        }
        public void AddComment(int id, string commentText, string posterName, string postingPass)
        {
            ekbConfig config =  GetConfiguration();

            if(postingPass != config.PostingPassword)
            {
                throw new BadPasswordException();
            }

            commentText = commentText.Trim();
            if (commentText.Length > 255)
            {
                commentText = commentText.Substring(0, 255);
            }

            posterName = posterName.Trim();
            if (posterName.Length > 50)
            {
                posterName = posterName.Substring(0, 50);
            }
            

            ekbComment comment = new ekbComment();
            
            comment.Comment = commentText;
            comment.Name = posterName;
            comment.TimeStamp = DateTime.Now.ToUniversalTime();
            comment.KillID = id;
            dc.ekbComments.InsertOnSubmit(comment);
            dc.SubmitChanges();

        }


    }
}
